package com.isyscore.os.metadata.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.isyscore.boot.mybatis.PageRequest;
import com.isyscore.device.common.model.RespDTO;
import com.isyscore.os.core.entity.SqlQuery;
import com.isyscore.os.core.exception.DataFactoryException;
import com.isyscore.os.core.exception.ErrorCode;
import com.isyscore.os.core.mapper.SqlQueryMapper;
import com.isyscore.os.metadata.constant.CommonConstant;
import com.isyscore.os.metadata.database.AbstractDatabase;
import com.isyscore.os.metadata.manager.DatabaseManager;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.dto.SqlParseDTO;
import com.isyscore.os.metadata.model.vo.DataQueryVO;
import com.isyscore.os.metadata.model.vo.ResultVO;
import com.isyscore.os.metadata.service.DataSourceService;
import com.isyscore.os.metadata.service.SqlQueryService;
import com.isyscore.os.metadata.utils.MapVariablesParserUtils;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 服务实现类
 *
 * @author starzyn
 * @since 2022-03-16
 */
@Service
@RequiredArgsConstructor
public class SqlQueryServiceImpl extends ServiceImpl<SqlQueryMapper, SqlQuery> implements SqlQueryService {

    public static final String SEARCH_STR = ";";
    private final DataSourceService dataSourceService;

    private final DatabaseManager databaseManager;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public RespDTO add(SqlQuery sqlQuery) {
        DataSourceDTO dataSourceDTO = dataSourceService.get(sqlQuery.getDataSourceId(), true);
        if (dataSourceDTO == null) {
            throw new DataFactoryException(ErrorCode.DATA_NOT_FOUND);
        }
        dataSourceDTO.setSelectDatabaseName(sqlQuery.getDatabaseName());
        boolean check= checkStatement(sqlQuery, dataSourceDTO);
        if (check) {
            sqlQuery.setStatus(CommonConstant.TRUE);
        } else {
            sqlQuery.setStatus(CommonConstant.FALSE);
        }
        //默认是私有的，后续可在接口初拓展权限相关的操作
        sqlQuery.setIsPrivate(CommonConstant.PRIVATE);
        //如果没有选择分组，则分配默认分组
        if (ObjectUtil.isNull(sqlQuery.getGroupId())) {
            sqlQuery.setGroupId(CommonConstant.DEFAULT_SQL_GROUP_ID);
        }
        this.save(sqlQuery);
        if (check) {
            return RespDTO.onSuc(ImmutableMap.of(
                    "code", ErrorCode.OK.getCode(),
                    "message", ErrorCode.OK.getMessage(),
                    "id", sqlQuery.getId()
            ));
        } else {
            return RespDTO.onSuc(ImmutableMap.of(
                    "code", ErrorCode.PRECHECK_ERROR.getCode(),
                    "message", ErrorCode.PRECHECK_ERROR.getMessage(),
                    "id", sqlQuery.getId()
            ));
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public RespDTO updateDataText(SqlQuery sqlQuery) {
        DataSourceDTO dataSourceDTO = dataSourceService.get(sqlQuery.getDataSourceId(), true);
        if(StrUtil.isNotBlank(sqlQuery.getDatabaseName())){
            dataSourceDTO.setDatabaseNameList(dataSourceDTO.getDatabaseNameList().stream().filter(x->sqlQuery.getDatabaseName().equals(x)).collect(Collectors.toList()));
        }
        boolean check=checkStatement(sqlQuery, dataSourceDTO);
        if (check) {
            sqlQuery.setStatus(CommonConstant.TRUE);
        } else {
            sqlQuery.setStatus(CommonConstant.FALSE);
        }
        //如果没有选择分组，则分配默认分组
        if (ObjectUtil.isNull(sqlQuery.getGroupId())) {
            sqlQuery.setGroupId(CommonConstant.DEFAULT_SQL_GROUP_ID);
        }
        this.updateById(sqlQuery);
        if (check) {
            return RespDTO.onSuc(ImmutableMap.of(
                    "id", sqlQuery.getId()
            ));
        } else {
            return RespDTO.onFail(ErrorCode.PRECHECK_ERROR);
        }
    }

    /**
     * <p>
     * 通过 SQL Text ID 查询数据
     * </p>
     */
    @Override
    public ResultVO getData(String id, PageRequest page, HttpServletRequest request) {
        //原始参数
        Map<String,String> params= Maps.newHashMap();
        //和sql进行验证后的参数
        Map<String,String> validateParams= Maps.newLinkedHashMap();
        Enumeration<String> enu=request.getParameterNames();
        while(enu.hasMoreElements()){
            String paraName=enu.nextElement();
            params.put(paraName,request.getParameter(paraName));
        }
        SqlQuery sqlQuery = getById(id);
        if (sqlQuery == null) {
            throw new DataFactoryException(ErrorCode.DATA_NOT_FOUND);
        }
        if (Objects.equals(sqlQuery.getStatus(), CommonConstant.FALSE)) {
            throw new DataFactoryException(ErrorCode.DATA_TEXT_ERROR);
        }
        // datasource
        DataSourceDTO dataSourceDTO = dataSourceService.get(sqlQuery.getDataSourceId(), true);
        dataSourceDTO.setSelectDatabaseName(sqlQuery.getDatabaseName());

        final DataQueryVO dataQueryVO = new DataQueryVO();
        //去除sql中的;防止查询失败
        String sql=sqlQuery.getSqlText();
        if(StrUtil.contains(sql,SEARCH_STR)){
            sql=StrUtil.removeAll(sql,SEARCH_STR);
        }
        //数据校验
        SqlParseDTO parse = MapVariablesParserUtils.parse(sql, Maps.newHashMap());
        for(String variable: parse.getSqlVariables()){
            if(ObjectUtil.isNull(params.get(variable))){
                throw new DataFactoryException(ErrorCode.SQL_QUERY_PARAM_ERROR,variable);
            }else {
                validateParams.put(variable,params.get(variable));
            }
        }
        dataQueryVO.setSize(page.getSize());
        dataQueryVO.setCurrent(page.getCurrent());
        dataQueryVO.setSqlText(sql);
        dataQueryVO.setSearchCount(true);
        dataQueryVO.setParams(validateParams);
        //sql data
        ResultVO res = databaseManager.getTableData(dataSourceDTO, dataQueryVO);
        //处理res 数据结构
        res.getHead().parallelStream().forEach(col -> {
            col.setStatisticsType(null);
            col.setTableName(null);
            col.setIsNumber(null);
            col.setIndex(null);
            col.setName(null);
        });
        return res;
    }



    /**
     * <p>
     * 预校验 SQL
     * </p>
     */
    public boolean checkStatement(SqlQuery sqlQuery, DataSourceDTO dataSourceDTO) {
        String sql = sqlQuery.getSqlText();
        if (!StringUtils.isEmpty(sql) && !StringUtils.isEmpty(sqlQuery.getDatabaseName())) {
            if (sql.trim().toUpperCase().startsWith("SELECT")) {
                if (!isPage(sql)) {
                    sql = pageSQL(sql, dataSourceDTO);
                }
                //去除sql中的;防止查询失败
                if(StrUtil.contains(sql, SEARCH_STR)){
                    sql= StrUtil.removeAll(sql,SEARCH_STR);
                }
                try {
                    SqlParseDTO parse = MapVariablesParserUtils.parse(sql, Maps.newHashMap());
                    if(CollectionUtil.isNotEmpty(parse.getSqlVariables())){
                        Map param=Maps.newLinkedHashMap();
                        for(String variable: parse.getSqlVariables()){
                            param.put(variable,null);
                        }
                        execSQL(sql, dataSourceDTO,param);
                    }else {
                        execSQL(sql, dataSourceDTO);
                    }
                } catch (Throwable e) {
                    log.error("sql 预校验错误：{}", e);
                    return false;
                }
                return true;
            }
        }
        return false;
    }

    /**
     * <p>
     * 判断SQL语句是否原生已经存在分页操作
     * </p>
     */
    public boolean isPage(String sql) {
        if (!StringUtils.isEmpty(sql)) {
            if (sql.trim().toUpperCase().contains("LIMIT")) {
                return true;
            }
            if (sql.trim().toUpperCase().contains("OFFSET")) {
                return true;
            }
        }
        return false;
    }

    /**
     * <p>
     * 执行SQL
     * </p>
     */
    public ResultVO execSQL(String sql, DataSourceDTO dataSourceDTO) {
        AbstractDatabase db = DatabaseManager.findDb(dataSourceDTO);
        return db.execSqlAndGet(dataSourceDTO, sql);
    }
    public ResultVO execSQL(String sql, DataSourceDTO dataSourceDTO,Map<String,String> params) {
        AbstractDatabase db = DatabaseManager.findDb(dataSourceDTO);
        return db.execSqlAndGetByParams(dataSourceDTO, sql,params);
    }
    public String pageSQL(String sql, DataSourceDTO dataSourceDTO) {
        AbstractDatabase db = DatabaseManager.findDb(dataSourceDTO);
        return db.pageSql(sql, 1, 1);
    }

    @Override
    public void importData(List<SqlQuery> sqlQueries) {
        if (sqlQueries.isEmpty()) {
            return;
        }
        Set<Long> sqlQuerySets = Sets.newConcurrentHashSet();
        for (SqlQuery sqlQuery : sqlQueries) {
            sqlQuerySets.add(sqlQuery.getId());
        }
        //删除模型
        LambdaQueryWrapper<SqlQuery> sqlQueryLambdaQueryWrapper = new LambdaQueryWrapper<>();
        sqlQueryLambdaQueryWrapper.in(SqlQuery::getId, sqlQuerySets);
        this.remove(sqlQueryLambdaQueryWrapper);
        this.saveBatch(sqlQueries);
    }
}
